﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.OleDb;
using System.Data;

namespace SOA.DAL
{
    public class Db
    {
        private String _connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents_and_Settings\Administrador\Meus_documentos\WS\SOA\SOA\App_Data\DBBestDog.mdb";
        public DataTable Query(string sql)
        {
            DataSet _dtSet =  new DataSet();
            
            OleDbDataAdapter _dtAdap = new OleDbDataAdapter(sql, _connectionString);
           
            _dtAdap.Fill(_dtSet);
            return _dtSet.Tables.Count > 0 ? _dtSet.Tables[0] : null;
        }

        public int Execute(string sql)
        {            
            OleDbConnection _connection = new OleDbConnection(_connectionString);
            
            OleDbCommand _command = new OleDbCommand(sql, _connection);
            var retorno = 0;
            Exception exception = null;
            //Abra a conexão
            _connection.Open();
            try
            {                
                retorno = _command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                exception = ex;
            }
            finally
            {                
                _connection.Close();
            }

            if (exception != null)
                throw exception;

            return retorno;
        }

        public object ExecuteScalar(string sql)
        {
            OleDbConnection _connection = new OleDbConnection(_connectionString);

            OleDbCommand _command = new OleDbCommand(sql, _connection);
            object retorno= null;
            Exception exception = null;
            //Abra a conexão
            _connection.Open();
            try
            {
                retorno = _command.ExecuteScalar();
            }
            catch (Exception ex)
            {
                exception = ex;
            }
            finally
            {
                _connection.Close();
            }

            if (exception != null)
                throw exception;

            return retorno;
        }
    }
}